package com.mvc.core.utils.excel;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.concurrent.RecursiveAction;

import org.apache.commons.lang3.reflect.FieldUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.PrintSetup;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.ss.util.CellUtil;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.mvc.core.utils.excel.verify.AbstractVerifyBuidler;



public class ExcelUtils {

	private static void printSetup(Sheet sheet) {
		PrintSetup printSetup = sheet.getPrintSetup();
		// 打印方向，true：横向，false：纵向
		printSetup.setLandscape(true);
		sheet.setFitToPage(true);
		sheet.setHorizontallyCenter(true);
	}

	private static Map<String, CellStyle> initStyles(Workbook wb) {
		Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
		CellStyle style;
		Font titleFont = wb.createFont();
		titleFont.setFontHeightInPoints((short) 15);
		titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
		style = wb.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 左右居中
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 上下居中
		style.setFont(titleFont);
		styles.put("title", style);

		Font monthFont = wb.createFont();
		monthFont.setFontName("Arial");
		monthFont.setFontHeightInPoints((short) 10);
		monthFont.setColor(IndexedColors.WHITE.getIndex());
		style = wb.createCellStyle();
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		style.setFont(monthFont);
		style.setWrapText(true);
		styles.put("header", style);

		style = wb.createCellStyle();
		Font cellFont = wb.createFont();
		cellFont.setFontName("Arial");
		cellFont.setFontHeightInPoints((short) 10);
		style.setFont(cellFont);
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setWrapText(true);
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		styles.put("cell", style);

		style = wb.createCellStyle();
		style.setFont(cellFont);
		style.setAlignment(CellStyle.ALIGN_LEFT);
		style.setWrapText(true);
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		styles.put("leftText", style);

		style = wb.createCellStyle();
		style.setFont(cellFont);
		style.setAlignment(CellStyle.ALIGN_RIGHT);
		style.setWrapText(true);
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		styles.put("rightText", style);

		style = wb.createCellStyle();
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
		styles.put("formula", style);

		style = wb.createCellStyle();
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
		styles.put("formula_2", style);

		style = wb.createCellStyle();
		style.setAlignment(CellStyle.ALIGN_CENTER);
		style.setWrapText(true);
		style.setBorderRight(CellStyle.BORDER_THIN);
		style.setRightBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderLeft(CellStyle.BORDER_THIN);
		style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderTop(CellStyle.BORDER_THIN);
		style.setTopBorderColor(IndexedColors.BLACK.getIndex());
		style.setBorderBottom(CellStyle.BORDER_THIN);
		style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
		style.setFillPattern(CellStyle.SOLID_FOREGROUND);
		style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
		styles.put("yellowcell", style);

		return styles;
	}

	/**
	 * @param list
	 *            数据源
	 * @param hearderRules
	 *            （如带序号，在规则里设置序头） 表头规则
	 * @param autoNum
	 *            带序号
	 * @param fields
	 *            字段，长度
	 * @return
	 * @throws Exception
	 */
	private static <T> Workbook createWorkbook(List<T> list, boolean autoNum, ExportRules hearderRules, Object[][] fields, ExportSheetCallback callBack) throws Exception {
		Workbook wb = new XSSFWorkbook();// 2007
		// Workbook wb = new HSSFWorkbook();//2003
		Map<String, CellStyle> styles = ExcelUtils.initStyles(wb);
		Sheet sheet = wb.createSheet();
		ExcelUtils.printSetup(sheet);
		int maxColumns = hearderRules.getMaxColumns();
		int maxRows = hearderRules.getMaxRows();
		if (hearderRules.getIfMerge()) {// 合并模式
			// header
			HashMap<String, String> rules = hearderRules.getHeaderRules();
			for (int i = 0; i < maxRows; i++) {
				sheet.createRow(i);
			}
			Iterator<Entry<String, String>> entries = rules.entrySet().iterator();
			while (entries.hasNext()) {
				Entry<String, String> entry = entries.next();
				String key = entry.getKey();
				String value = entry.getValue();
				Object[] range = coverRange(key);
				CellRangeAddress cra = new CellRangeAddress((int) range[0] - 1, (int) range[1] - 1, POIConstant.cellRefNums.get(range[2]), POIConstant.cellRefNums.get(range[3]));
				sheet.addMergedRegion(cra);
				RegionUtil.setBorderBottom(1, cra, sheet, wb); // 下边框
				RegionUtil.setBorderLeft(1, cra, sheet, wb); // 左边框
				RegionUtil.setBorderRight(1, cra, sheet, wb); // 有边框
				RegionUtil.setBorderTop(1, cra, sheet, wb); // 上边框
				if ((maxColumns - 1) == POIConstant.cellRefNums.get(range[3]) - POIConstant.cellRefNums.get(range[2])) {// 占满全格，则为表头
					CellUtil.createCell(sheet.getRow((int) range[0] - 1), POIConstant.cellRefNums.get(range[2]), value, styles.get("title"));
				} else {
					CellUtil.createCell(sheet.getRow((int) range[0] - 1), POIConstant.cellRefNums.get(range[2]), value, styles.get("header"));
				}
			}
		} else {// 非合并
			if (hearderRules.getTitile() == null) {
				sheet.createRow(0);
				String[] hearder = hearderRules.getHearder();
				for (int i = 0; i < hearder.length; i++) {
					CellUtil.createCell(sheet.getRow(0), i, hearder[i], styles.get("header"));
				}
			} else {
				sheet.createRow(0);
				sheet.createRow(1);
				CellRangeAddress cra = new CellRangeAddress(0, 0, 0, maxColumns);
				sheet.addMergedRegion(cra);
				CellUtil.createCell(sheet.getRow(0), 0, hearderRules.getTitile(), styles.get("title"));
				String[] hearder = hearderRules.getHearder();
				for (int i = 0; i < hearder.length; i++) {
					CellUtil.createCell(sheet.getRow(1), i, hearder[i], styles.get("header"));
				}
			}

		}
		// set width
		for (int i = 0; i < fields.length; i++) {
			if (autoNum) {
				sheet.setColumnWidth(0, 2000);
			}
			Object[] columnsWidth = fields[i];
			sheet.setColumnWidth(i + (autoNum ? 1 : 0), (int) columnsWidth[1]);
		}
		// body row
		for (int i = 0; i < list.size(); i++) {
			Row row = sheet.createRow(i + maxRows);
			T t = list.get(i);
			for (int j = 0, n = 0; n < fields.length; j++, n++) {
				Cell cell = row.createCell(j);
				cell.setCellStyle(styles.get("cell"));
				// 有效数据行号
				if (autoNum && j == 0) {
					cell.setCellValue(i + 1);
					n--;
					continue;
				}
				// 读取Map/Object对应字段值
				Object value = null;
				if (t instanceof Map) {
					value = ((Map) t).get(fields[n][0]);
				} else {
					value = FieldUtils.readDeclaredField(t, (String) fields[n][0], true);
				}
				if (value == null) {
					value = "";
				}
				// 填充列值
				CellStyle style = styles.get("cell");
				if (callBack != null) {
					value = callBack.callback(fields[n][0].toString(), value, style);
				}
				setCellValue(style, value, cell);
			}
		}
		// footer row
		if (hearderRules.getIfFooter()) {
			HashMap<String, String> footerRules = hearderRules.getFooterRules();
			// 构建尾行数字
			int currRownum = hearderRules.getMaxRows() + list.size();
			int[] footerNum = getFooterNum(footerRules.entrySet().iterator(), currRownum);
			Iterator<Entry<String, String>> entries = footerRules.entrySet().iterator();
			for (int i = 0; i < footerNum.length; i++) {
				sheet.createRow(footerNum[i]);
			}
			while (entries.hasNext()) {
				Entry<String, String> entry = entries.next();
				String key = entry.getKey();
				String value = entry.getValue();
				Object[] range = coverRange(key);
				CellRangeAddress cra = new CellRangeAddress((int) range[0] + currRownum - 1, (int) range[1] + currRownum - 1, POIConstant.cellRefNums.get(range[2]),
						POIConstant.cellRefNums.get(range[3]));
				sheet.addMergedRegion(cra);
				RegionUtil.setBorderBottom(1, cra, sheet, wb); // 下边框
				RegionUtil.setBorderLeft(1, cra, sheet, wb); // 左边框
				RegionUtil.setBorderRight(1, cra, sheet, wb); // 有边框
				RegionUtil.setBorderTop(1, cra, sheet, wb); // 上边框
				String cellValue = "";
				CellStyle style = styles.get("cell");
				cellValue = value;
				CellUtil.createCell(sheet.getRow((int) range[0] + currRownum - 1), POIConstant.cellRefNums.get(range[2]), cellValue, style);
			}

		}
		return wb;
	}

	/**
	 * 给单元格设置值
	 * 
	 * @param value
	 *            列值
	 * @param pattern
	 *            格式化值
	 * @param cell
	 *            单元格
	 */
	private static void setCellValue(CellStyle style, Object value, Cell cell) {
		cell.setCellStyle(style);
		// 判断值的类型后进行强制类型转换
		if (value instanceof String) {
			cell.setCellValue(String.valueOf(value));
		} else if (value instanceof Integer) {
			cell.setCellValue((Integer) (value));
		} else if (value instanceof Double) {
			cell.setCellValue((Double) (value));
		} else if (value instanceof Long) {
			cell.setCellValue((Long) (value));
		} else if (value instanceof Date) {
			cell.setCellValue(new SimpleDateFormat(POIConstant.FMTDATETIME).format((Date) (value)));
		} else if (value == null) {
			cell.setCellValue("");
		} else {
			cell.setCellValue(String.valueOf(value));
		}
	}

	/**
	 * 合并单元格转换
	 * 
	 * @param obj
	 * @return
	 */
	private static Object[] coverRange(Object obj) {
		String[] range = ((String) obj).split(",");
		Object[] rangeInt = new Object[4];
		for (int i = 0; i < range.length; i++) {
			if (i < 2) {
				rangeInt[i] = Integer.parseInt(range[i]);
			} else {
				rangeInt[i] = range[i];
			}

		}
		return rangeInt;
	}

	/**
	 * 根据页脚数据获得行号
	 * 
	 * @param entries
	 * @param currRownum
	 * @return
	 */
	private static int[] getFooterNum(Iterator<Entry<String, String>> entries, int currRownum) {
		int row = 0;
		while (entries.hasNext()) {
			Entry<String, String> entry = entries.next();
			String key = entry.getKey();
			Object[] range = coverRange(key);
			int a = (int) range[1];
			row = a > row ? a : row;
		}
		int[] footerNum = new int[row];
		for (int i = 0; i < row; i++) {
			footerNum[i] = currRownum + i;
		}
		return footerNum;
	}

	/**
	 * 导出带回调处理的
	 * 
	 * @param <T>
	 * 
	 * @param data
	 *            数据源
	 * @param exportRules
	 *            导出规则
	 * @return
	 */
	public static <T> Workbook createWorkbook(List<T> data, ExportRules exportRules, ExportSheetCallback callBack) throws Exception {
		return createWorkbook(data, exportRules.getAutoNum(), exportRules, exportRules.getFields(), callBack);
	}

	/**
	 * 导出
	 * 
	 * @param <T>
	 * 
	 * @param data
	 *            数据源
	 * @param exportRules
	 *            导出规则
	 * @return
	 */
	public static <T> Workbook createWorkbook(List<T> data, ExportRules exportRules) throws Exception {
		return createWorkbook(data, exportRules.getAutoNum(), exportRules, exportRules.getFields(), null);
	}

	/**
	 * 解析Sheet
	 * 
	 * @param clss
	 *            结果bean
	 * @param verifyBuilder
	 *            校验器
	 * @param sheet
	 * @param dataStartRow
	 *            开始行:从0开始计
	 * @return
	 * @throws Exception
	 */
	public static <T> ImportRspInfo<T> parseSheet(Class<T> clss, AbstractVerifyBuidler verifyBuilder, Sheet sheet, int dataStartRow, int endRowNum) throws Exception {
		return parseSheet(clss, verifyBuilder, sheet, dataStartRow, endRowNum, null);
	}

	/**
	 * 解析Sheet
	 * 
	 * @param clss
	 *            结果bean
	 * @param verifyBuilder
	 *            校验器
	 * @param sheet
	 * @param dataStartRow
	 *            开始行
	 * @param callback
	 *            加入回调逻辑
	 * @return
	 * @throws Exception
	 */
	public static <T> ImportRspInfo<T> parseSheet(Class<T> clss, AbstractVerifyBuidler verifyBuilder, Sheet sheet, int dataStartRow, int endRowNum, ParseSheetCallback<T> callback) {
		ImportRspInfo<T> rsp = new ImportRspInfo<T>();
		List<T> beans = new ArrayList<>();
		StringBuffer errors = new StringBuffer();
		StringBuffer rowErrors = new StringBuffer();
		try {
			int rowStart = sheet.getFirstRowNum() + dataStartRow;
			int rowEnd = sheet.getLastRowNum() - endRowNum;
			for (int rowNum = rowStart; rowNum <= rowEnd; rowNum++) {
				Row r = sheet.getRow(rowNum);
				// 创建对象
				T t = clss.newInstance();
				int fieldNum = 0;
				for (int cellNum : POIConstant.convertToCellNum(verifyBuilder.cellRefs)) {
					// 列坐标
					CellReference cellRef = new CellReference(rowNum, cellNum);
					try {
						Object cellValue = getCellValue(r, cellNum);
						// 校验和格式化列值
						cellValue = verifyBuilder.verify(verifyBuilder.filedNames[fieldNum], cellValue);
						// 填充列值
						FieldUtils.writeDeclaredField(t, verifyBuilder.filedNames[fieldNum], cellValue, true);
					} catch (Exception e) {
						if (e instanceof MessageException) {
							rowErrors.append(cellRef.formatAsString()).append(":").append(e.getMessage()).append("\t");
						}
					}
					fieldNum++;
				}
				// 回调处理一下特殊逻辑
				if (callback != null) {
					callback.callback(t, rowNum);
				}
				beans.add(t);
				if (rowErrors.length() > 0) {
					errors.append(rowErrors).append("\r\n");
					rowErrors.setLength(0);
				}
			}
		} catch (Exception e) {

		} finally {
			// throw parse exception
			if (errors.length() > 0) {
				rsp.setSuccess(false);
				rsp.setMessage(errors.toString());
			}
			rsp.setData(beans);
		}

		// 返回结果
		return rsp;
	}

	public static Object getCellValue(Row r, int cellNum) {
		// 缺失列处理政策
		Cell cell = r.getCell(cellNum, Row.CREATE_NULL_AS_BLANK);
		Object obj = null;
		switch (cell.getCellType()) {
		case Cell.CELL_TYPE_STRING:
			obj = cell.getRichStringCellValue().getString();
			break;
		case Cell.CELL_TYPE_NUMERIC:
			if (DateUtil.isCellDateFormatted(cell)) {
				obj = cell.getDateCellValue();
			} else {
				obj = cell.getNumericCellValue();
			}
			break;
		case Cell.CELL_TYPE_BOOLEAN:
			obj = cell.getBooleanCellValue();
			break;
		case Cell.CELL_TYPE_FORMULA:
			obj = cell.getCellFormula();
			break;
		}
		return obj;
	}

	public static class FastExport<T> extends RecursiveAction {
		/**
		 * 
		 */
		private static final long serialVersionUID = 1L;
		/**
		 * 每个"小任务"最多只打印20个数
		 */
		private static final int MAX = 1000;
		private List<T> list = null;
		private int start;
		private int end;

		public FastExport(int start, int end, List<T> list) {
			this.start = start;
			this.end = end;
			this.list = list;
		}

		@Override
		protected void compute() {
			// 当end-start的值小于MAX时，开始打印
			if ((end - start) < MAX) {
				for (int i = start; i < end; i++) {
					System.out.println(Thread.currentThread().getName() + "i的值" + i);
				}
			} else {
				// 将大任务分解成两个小任务
				int middle = (start + end) / 2;
				FastExport<T> left = new FastExport<T>(start, middle, list);
				FastExport<T> right = new FastExport<T>(middle, end, list);
				invokeAll(left, right);
			}
		}
	}
}
